package slh.comment;

import slh.ConSql;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class commentDao {
    private Connection conn;

    public commentDao() {
        try {
            this.conn = ConSql.getConnection(); // 初始化数据库连接
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("Failed to initialize database connection", e);
        }
    }

    // 添加评论
    public boolean addComment(comment comment) throws SQLException {
        String sql = "INSERT INTO comments (userid, content_id, content_type, rating, comment_text) VALUES (?, ?, ?, ?, ?)";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, comment.getUserId());
            stmt.setInt(2, comment.getContentId());
            stmt.setString(3, comment.getContentType());
            stmt.setInt(4, comment.getRating());
            stmt.setString(5, comment.getCommentText());
            return stmt.executeUpdate() > 0;
        }
    }

    // 更新评论
    public boolean updateComment(comment comment) throws SQLException {
        String sql = "UPDATE comments SET rating = ?, comment_text = ? WHERE comment_id = ? AND userid = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, comment.getRating());
            stmt.setString(2, comment.getCommentText());
            stmt.setInt(3, comment.getCommentId());
            stmt.setString(4, comment.getUserId());
            return stmt.executeUpdate() > 0;
        }
    }

    // 删除评论(软删除)
    public boolean deleteComment(int commentId, String userId) throws SQLException {
        String sql = "UPDATE comments SET is_deleted = TRUE WHERE comment_id = ? AND userid = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, commentId);
            stmt.setString(2, userId);
            return stmt.executeUpdate() > 0;
        }
    }

    // 获取用户的所有评论
    public List<comment> getUserComments(String userId) throws SQLException {
        List<comment> comments = new ArrayList<>();
        String sql = "SELECT c.*, u.avatar FROM comments c JOIN users u ON c.userid = u.userid " +
                "WHERE c.userid = ? AND c.is_deleted = FALSE ORDER BY c.created_at DESC";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, userId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                comment comment = mapRowToComment(rs);
                comments.add(comment);
            }
        }
        return comments;
    }

    public ArrayList<String> getUserAllCommentsText(String userId) throws SQLException {
        List<comment> comments = new ArrayList<>();
        String sql = "SELECT c.*, u.avatar FROM comments c JOIN users u ON c.userid = u.userid " +
                "WHERE c.userid = ? AND c.is_deleted = FALSE ORDER BY c.created_at DESC";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, userId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                comment comment = mapRowToComment(rs);
                comments.add(comment);
            }
        }
//        return comments;
        ArrayList<String> commentTextList = new ArrayList<>();
        for(comment comment : comments) {
            commentTextList.add(comment.getCommentText());
        }
        return commentTextList;
    }

    public static List<comment> getCommentsByContent(int contentId, String contentType)
            throws SQLException {
        List<comment> comments = new ArrayList<>();
        String sql = "SELECT c.*, u.AVATAR " +
                "FROM Comments c " +
                "JOIN Users u ON c.USERID = u.USERID " +
                "WHERE c.CONTENT_ID = ? AND c.CONTENT_TYPE = ? AND c.IS_DELETED = 0";

        try (Connection conn = ConSql.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, contentId);
            stmt.setString(2, contentType);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    comment comment = new comment();
                    comment.setCommentId(rs.getInt("COMMENT_ID"));
                    comment.setUserId(rs.getString("USERID"));
                    comment.setContentId(rs.getInt("CONTENT_ID"));
                    comment.setContentType(rs.getString("CONTENT_TYPE"));
                    comment.setRating(rs.getInt("RATING"));
                    comment.setCommentText(rs.getString("COMMENT_TEXT"));
                    comment.setCreatedAt(rs.getTimestamp("CREATED_AT"));
                    comment.setAvatar(rs.getString("AVATAR"));
                    comments.add(comment);
                }
            }
        }
        return comments;
    }
    // 获取内容的评论
    public List<comment> getContentComments(int contentId, String contentType) throws SQLException {
        List<comment> comments = new ArrayList<>();
        String sql = "SELECT c.*, u.avatar FROM comments c JOIN users u ON c.userid = u.userid " +
                "WHERE c.content_id = ? AND c.content_type = ? AND c.is_deleted = FALSE " +
                "ORDER BY c.created_at DESC";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, contentId);
            stmt.setString(2, contentType);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                comment comment = mapRowToComment(rs);
                comments.add(comment);
            }
        }
        return comments;
    }

    // 获取内容的平均评分
    public double getAverageRating(int contentId, String contentType) throws SQLException {
        String sql = "SELECT AVG(rating) as avg_rating FROM comments " +
                "WHERE content_id = ? AND content_type = ? AND is_deleted = FALSE";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, contentId);
            stmt.setString(2, contentType);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getDouble("avg_rating");
            }
            return 0;
        }
    }
    public int[] getRatingDistribution(int contentId, String contentType) throws SQLException {
        int[] ratingCounts = new int[5]; // 初始化为0
        String sql = "SELECT RATING, COUNT(*) as count FROM comments WHERE CONTENT_ID = ? AND CONTENT_TYPE = ? AND IS_DELETED = 0 GROUP BY RATING ORDER BY RATING";

        try (Connection conn= ConSql.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, contentId);
            stmt.setString(2, contentType);

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    int rating = rs.getInt("rating");
                    if (rating >= 1 && rating <= 5) {
                        ratingCounts[rating - 1] = rs.getInt("count");
                    }
                }
            }
        }
        return ratingCounts;
    }


    // 获取用户对特定内容的评论
    public comment getUserContentComment(String userId, int contentId, String contentType) throws SQLException {
        String sql = "SELECT * FROM comments WHERE USERID = ? AND CONTENT_ID = ? AND CONTENT_TYPE= ? AND IS_DELETED = 0";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, userId);
            stmt.setInt(2, contentId);
            stmt.setString(3, contentType);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return mapRowToComment(rs);
            }
            return null;
        }
    }

    public void updateMovieRating(int movieId) throws SQLException {
        // 计算新的平均评分
        String avgSql = "SELECT AVG(RATING) as avg_rating FROM comments WHERE CONTENT_ID = ? AND CONTENT_TYPE = 'movie' AND IS_DELETED = 0";
        String updateSql = "UPDATE movie SET SCORE = ?, RATING_COUNT = (SELECT COUNT(*) FROM comments WHERE CONTENT_ID = ? AND CONTENT_TYPE = 'movie' AND IS_DELETED = 0) WHERE MOVIE_ID = ?";

        try (Connection conn = ConSql.getConnection();
             PreparedStatement avgStmt = conn.prepareStatement(avgSql);
             PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {

            avgStmt.setInt(1, movieId);
            ResultSet rs = avgStmt.executeQuery();

            if (rs.next()) {
                double averageRating = rs.getDouble("avg_rating");

                updateStmt.setDouble(1, averageRating*2);
                updateStmt.setInt(2, movieId);
                updateStmt.setInt(3, movieId);
                updateStmt.executeUpdate();
            }
        }
    }
    public void updateBookRating(int bookId) throws SQLException {
        // 计算新的平均评分
        String avgSql = "SELECT AVG(RATING) as avg_rating FROM comments WHERE CONTENT_ID = ? AND CONTENT_TYPE = 'movie' AND IS_DELETED = 0";
        String updateSql = "UPDATE book SET SCORE = ?, RATING_COUNT = (SELECT COUNT(*) FROM comments WHERE CONTENT_ID = ? AND CONTENT_TYPE = 'movie' AND IS_DELETED = 0) WHERE BOOK_ID = ?";

        try (Connection conn = ConSql.getConnection();
             PreparedStatement avgStmt = conn.prepareStatement(avgSql);
             PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {

            avgStmt.setInt(1, bookId);
            ResultSet rs = avgStmt.executeQuery();

            if (rs.next()) {
                double averageRating = rs.getDouble("avg_rating");

                updateStmt.setDouble(1, averageRating*2);
                updateStmt.setInt(2, bookId);
                updateStmt.setInt(3, bookId);
                updateStmt.executeUpdate();
            }
        }
    }

    private comment mapRowToComment(ResultSet rs) throws SQLException {
        comment comment = new comment();
        comment.setCommentId(rs.getInt("comment_id"));
        comment.setUserId(rs.getString("userid"));
        comment.setContentId(rs.getInt("content_id"));
        comment.setContentType(rs.getString("content_type"));
        comment.setRating(rs.getInt("rating"));
        comment.setCommentText(rs.getString("comment_text"));
        comment.setCreatedAt(rs.getTimestamp("created_at"));
        comment.setDeleted(rs.getBoolean("is_deleted"));
        try {
            comment.setAvatar(rs.getString("avatar"));
        } catch (SQLException e) {
            // 如果avatar列不存在，忽略
        }
        return comment;
    }
    public void close() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}
class CommentStats {
    private int contentId;
    private String contentType;
    private double averageRating;
    private int[] ratingCounts; // 索引0-4对应1-5星
    private int totalComments;

    // 构造函数、getter和setter方法
    public CommentStats() {}

    public int getContentId() { return contentId; }
    public void setContentId(int contentId) { this.contentId = contentId; }
    public String getContentType() { return contentType; }
    public void setContentType(String contentType) { this.contentType = contentType; }
    public double getAverageRating() { return averageRating; }
    public void setAverageRating(double averageRating) { this.averageRating = averageRating; }
    public int[] getRatingCounts() { return ratingCounts; }
    public void setRatingCounts(int[] ratingCounts) { this.ratingCounts = ratingCounts; }
    public int getTotalComments() { return totalComments; }
    public void setTotalComments(int totalComments) { this.totalComments = totalComments; }
}